﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.ComponentModel;

namespace PlanningGIS.Util.Data
{
    /// <summary>
    /// 数据库操作
    /// </summary>
    public interface IDbContext
    {
        /// <summary>
        /// 获取或设置连接字符串. 
        /// Oracle: Data Source=orcl151;Persist Security Info=True;User ID=zjgismd;Password=zjgismd ；
        /// 或Oracle: Data Source=126.33.9.151/orcl;User ID=zjgismd;Password=zjgismd ；
        /// </summary>
        string ConnectionString { get; set; }

        /// <summary>
        /// 是否自动关闭数据库，默认为自动关闭。扩展此属性是为了方便在长事务中保持数据库连接而不自动关闭。
        /// </summary>
        bool AutoClosed { get; set; }

        /// <summary>
        /// 获取数据库连接对象
        /// </summary>        
        IDbConnection Connection { get; }
        
        /// <summary>
        /// 获取当前的数据库连接状态
        /// </summary>
        ConnectionState State { get; }

        /// <summary>
        /// 获取当前执行的消息
        /// </summary>
        string Message { get;}

        /// <summary>
        /// 是否可以连接到数据库
        /// </summary>
        /// <returns></returns>
        bool CanConncect();

        /// <summary>
        /// 创建数据表
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        /// <returns></returns>
        bool CreateTable(string tableName,List<DbFieldInfo> fields);

        /// <summary>
        /// 添加表字段
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        void AddFields(string tableName, List<DbFieldInfo> fields);

        /// <summary>
        /// 获取数据库中的所有数据表名
        /// </summary>
        /// <returns></returns>
        List<string> GetTableNames();

        /// <summary>
        /// 获取数据表中的字段信息
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        DataTable GetTableFields(string tablename);
        
        /// <summary>
        /// 获取数据表的字段信息
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        List<DbFieldInfo> GetFieldInfos(string tablename);

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="tablename">要查询的数据表</param>
        /// <param name="where">不带where的条件语句</param>
        /// <param name="filed">要统计的字段</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        long GetCount(string tablename, string where, string filed = "*", Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 获取字段的唯一值
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="fieldname">字段名称</param>
        /// <returns></returns>
        DataTable GetUniqueValue(string tablename, string fieldname);

        /// <summary>
        /// 获取所有满足条件的数据
        /// </summary>
        /// <typeparam name="T">数据实体对象</typeparam>
        /// <param name="parameters">条件参数</param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        DataTable GetAll<T>(Dictionary<string, dynamic> parameters = null, string tablename = "");

        /// <summary>
        /// 获取所有满足条件的数据实体对象
        /// </summary>
        /// <typeparam name="T">数据实体对象</typeparam>
        /// <param name="parameters">条件参数</param>
        /// <param name="tablename">数据表名称，默认为实例对象的名称</param>
        /// <returns></returns>
        List<T> GetAllEntry<T>(Dictionary<string, dynamic> parameters = null, string tablename = "");

        /// <summary>
        /// 执行Sql查询语句，获取满足条件的数据表信息
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>System.Data.DataTable</returns>
        DataTable GetDataTable(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 根据唯一标识获取数据
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="idvalue">唯一标识的值</param>
        /// <param name="idname">唯一标识的字段名称</param>
        /// <returns></returns>
        DataTable GetDataTableByID(string tablename, string idvalue, string idname = "id");

        /// <summary>
        /// 根据唯一标识获取对应实体对象的数据
        /// </summary>
        /// <typeparam name="T">数据实体对象</typeparam>
        /// <param name="idvalue">唯一标识的值</param>
        /// <param name="idname">唯一标识的字段名称</param>
        /// <param name="tablename">数据表名称，默认为实例对象的名称</param>
        /// <returns></returns>
        DataTable GetDataTableByID<T>(string idvalue, string idname = "id", string tablename = "");

        /// <summary>
        /// 根据唯一标识获取对应实体对象的实例
        /// </summary>
        /// <typeparam name="T">数据实体对象</typeparam>
        /// <param name="idvalue">唯一标识的值</param>
        /// <param name="idname">唯一标识的字段名称</param>
        /// <param name="tablename">数据表名称，默认为实例对象的名称</param>
        /// <returns></returns>
        T GetEntryByID<T>(string idvalue, string idname = "id", string tablename = "");

        /// <summary>
        /// 执行Sql语句或存储过程，过去满足条件的数据集记录。注意：数据使用完成后要关闭数据集
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        IDataReader GetDataReader(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 执行查询，返回查询结果中第一行的第一列。
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>查询结果中第一行的第一列</returns>
        object GetScalar(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 获取数据库系统时间
        /// </summary>
        /// <returns></returns>
        DateTime GetSysDateTime();

        /// <summary>
        /// 创建事务对象，事务执行完成后注意要提交事务，并关闭数据库
        /// </summary>
        void CreateTransaction();

        /// <summary>
        /// 创建数据操作命令
        /// </summary>
        /// <returns></returns>
        IDbCommand CreateCommand();
        
        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>受影响的行数。</returns>
        int ExecuteNonQuery(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 在数据库事务中将一条 SQL 语句执行多次。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        int ExecuteNonQuery(string sql, List<Dictionary<string, dynamic>> parameters, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 在数据库事务中执行多条 SQL 语句。
        /// </summary>
        /// <param name="sqls">多条 SQL 语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        int ExecuteNonQuery(List<string> sqls, List<Dictionary<string, dynamic>> parameters = null, CommandType cmdtype = CommandType.Text);

        /// <summary>
        /// 提交事务
        /// </summary>
        void CommitTransaction();
        
        /// <summary>
        /// 回滚提交事务
        /// </summary>
        void RollbackTransaction();
                
        /// <summary>
        /// 判断符合条件的记录是否存在。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <returns>符合条件的记录是否存在。</returns>
        bool Exists(string sql, Dictionary<string, dynamic> parameters = null);
        
        /// <summary>
        /// 是否存在指定的用户对象类型
        /// </summary>
        /// <param name="name">用户对象名称，表名、视图名等</param>
        /// <param name="type">对象类型</param>
        /// <returns></returns>
        bool ExistsObject(string name , zjgisDbObjectType type= zjgisDbObjectType.zjgisDbObjectTable);

        /// <summary>
        /// 将对象新增到数据库中
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="entity">对象实例</param>
        /// <param name="tablename">数据表名称，默认为空，表示取对象的名称</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Insert<T>(T entity, string tablename = "", bool checkfield = true);
        
        /// <summary>
        /// 将一条数据插入到指定的数据表中
        /// </summary>
        /// <param name="tablename">要插入到的数据表</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Insert(string tablename, Dictionary<string, dynamic> parameters, bool checkfield = true);

        /// <summary>
        /// 将多条数据插入到一个数据表中
        /// </summary>
        /// <param name="tablename">要插入到的数据表</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Insert(string tablename, List<Dictionary<string, dynamic>> parameters, bool checkfield = true);

        /// <summary>
        /// 在每个数据表中插入一条指定的数据
        /// </summary>
        /// <param name="tablenames">要插入到的数据表</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Insert(List<string> tablenames, List<Dictionary<string, dynamic>> parameters, bool checkfield = true);

        /// <summary>
        /// 在每个数据表中插入多条指定的数据
        /// </summary>
        /// <param name="tablenames">要插入到的数据表</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Insert(List<string> tablenames, List<List<Dictionary<string, dynamic>>> parameters, bool checkfield = true);

        /// <summary>
        /// 根据数据表实例对象更新指定项的值
        /// </summary>
        /// <typeparam name="T">数据表实体对象</typeparam>
        /// <param name="entity">对象实例</param>
        /// <param name="keyfield">唯一主键，默认为id</param>
        /// <param name="keyvalue">主键的值，默认从obj实例中取值</param>
        /// <param name="nonull">是否只取不为空的值，默认为true</param>
        /// <param name="tablename">数据表名称，默认为空，表示取对象的名称</param>
        /// <returns></returns>
        int Update<T>(T entity, string keyfield = "id", string keyvalue = "", bool nonull = true, string tablename = "");

        /// <summary>
        /// 更新数据表中满足条件的记录
        /// </summary>
        /// <param name="tablename">要更新的数据表</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">满足的条件</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Update(string tablename, Dictionary<string, dynamic> values, Dictionary<string, dynamic> wheres, bool checkfield = true);
        
        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">满足的条件</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, List<Dictionary<string, dynamic>> wheres, bool checkfield = true);
        
        /// <summary>
        /// 更新单个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablename">要更新的数据表</param>
        /// <param name="values">更新的值</param>
        /// <param name="where">自定义的条件</param>
        /// <param name="whereparameters">自定义条件中的参数</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Update(string tablename, Dictionary<string, dynamic> values, string where, Dictionary<string, dynamic> whereparameters, bool checkfield = true);
        
        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">自定义的条件</param>
        /// <param name="whereparameters">自定义条件中的参数</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, List<string> wheres, List<Dictionary<string, dynamic>> whereparameters, bool checkfield = true);
        
        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">满足的条件</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, Dictionary<string, dynamic> wheres, bool checkfield = true);
        
        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="where">自定义的条件</param>
        /// <param name="whereparameter">自定义条件中的参数</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, string where, Dictionary<string, dynamic> whereparameter, bool checkfield = true);
        
        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="datatable">要更新的数据表</param>
        /// <param name="sql">获取datatable的Sql语句</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        int UpdateDataTable(DataTable datatable, string sql, Dictionary<string, dynamic> parameters = null);

        /// <summary>
        /// 删除数据表中满足条件的记录
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="where">要删除的条件值，以等于判断</param>
        /// <returns></returns>
        int Delete(string tablename, Dictionary<string, dynamic> where = null);

        /// <summary>
        /// 删除数据表中指定编号的记录
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="idvalue">编号的值</param>
        /// <param name="idname">编号字段名称，默认为id</param>
        /// <returns></returns>
        int Delete(string tablename, string idvalue, string idname = "id");

        /// <summary>
        /// 删除数据表中指定编号的记录
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="ids">编号集合</param>
        /// <param name="idname">编号字段名称，默认为id</param>
        /// <returns></returns>
        int Delete(string tablename, List<string> ids, string idname = "id");

        /// <summary>
        /// 删除多个数据表中满足指定字段值的数据
        /// </summary>
        /// <param name="tablenames">数据表名称</param>
        /// <param name="values">值</param>
        /// <param name="fields">字段</param>
        /// <returns></returns>
        int Delete(List<string> tablenames, List<string> values, List<string> fields);

        /// <summary>
        /// 删除多个表中字段字段值为id的记录
        /// </summary>
        /// <param name="id">值</param>
        /// <param name="tablefields">数据表及对应的字段：table，field</param>
        /// <returns></returns>
        int Delete(string id, Dictionary<string, string> tablefields);

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        void DeleteTable(string tablename);

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="fieldName">字段名称</param>
        void DeleteField(string tablename, string fieldName);


        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        void Close();

        /// <summary>
        /// 压缩数据库
        /// </summary>
        void Compact();
    }

    /// <summary>
    /// 数据库对象类型
    /// </summary>
    [Description("数据库对象类型")]
    public enum zjgisDbObjectType
    {
        /// <summary>
        /// 数据表
        /// </summary>
        zjgisDbObjectTable = 0 ,
        /// <summary>
        /// 存储过程
        /// </summary>
        zjgisDbObjectProcedure,
        /// <summary>
        /// 视图
        /// </summary>
        zjgisDbObjectView,
        /// <summary>
        /// 所有类型
        /// </summary>
        zjgisDbObjectAll
    }
}
